PostgreSQL pg_bulkload 导入 CSV 格式数据

1 背景知识

本文介绍如何使用 pg_bulkload 扩展 导入 CSV 的格式数据。

2 CSV 格式数据的单进程导入

2.1 上传CSV 文件

此 isbn.csv 文件为 Zlibrary 的数据转换后的 csv 文件。请上传到 /soft 目录。

ls -l /soft/isbn.csv

//屏幕输出：
-rw-r--r-- 1 postgres postgres 253894432 May  2 18:57 /soft/isbn.csv

2.2 创建表

创建用于装载 csv 数据的表 zlibrary.isbn。

psql -U postgres -d testdb

DROP SCHEMA IF EXISTS zlibrary CASCADE;
CREATE SCHEMA zlibrary;

CREATE TABLE zlibrary.isbn (
    zlibrary_id integer NOT NULL,
    isbn character varying(13) NOT NULL
);


ALTER TABLE ONLY zlibrary.isbn
    ADD CONSTRAINT "PRIMARY_9E9D7BB2" PRIMARY KEY (zlibrary_id, isbn);


ALTER TABLE ONLY zlibrary.isbn
    ADD CONSTRAINT "isbn_id_2E99E8E3" UNIQUE (isbn, zlibrary_id);

2.3 创建控制文件

创建并编辑用于数据导入的控制文件 sample_csv.ctl 。

su - postgres 
vi /soft/sample_csv.ctl 

//输入内容：
#
# sample_csv.ctl -- Control file to load CSV input data
#
#    Copyright (c) 2007-2024, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
#
OUTPUT = zlibrary.isbn                # 导入数据所存放的目的地。
INPUT = /soft/isbn.csv                # 指定导入的数据来源和位置。绝对路径。    
TRUNCATE = YES                        # 清空数据目标表。
WRITER = DIRECT                       # 导入数据时，绕过共享缓冲区，并跳过WAL日志。 
TYPE = CSV                            # 导入数据的类型
QUOTE = "\""                          # 指定什么字符作为引号。
ESCAPE = \                            # 引号的转义符号。
DELIMITER = ","                       # CSV 文件的分隔符。

对于以上参数详细信息，请参考 pg_bulkload 控制文件参数。

2.4 导入数据

单进程导入数据的耗时为 1m14.976s。

time pg_bulkload -U postgres -d testdb /soft/sample_csv.ctl

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        12180911 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

real    1m14.976s
user    0m0.002s
sys     0m0.006s

3 CSV 格式的数据的多进程导入

3.1 上传CSV 文件

此 isbn.csv 文件为 Zlibrary 的数据库转换后的 csv 文件。请上传到 /soft 目录。

ls -l /soft/isbn.csv

//屏幕输出：
-rw-r--r-- 1 postgres postgres 253894432 May  2 18:57 /soft/isbn.csv

3.2 创建控制文件

编写数据导入所需的控制文件 sample_csv.ctl 。新添加了 MULTI_PROCESS=YES 参数。

su - postgres 
vi /soft/sample_csv.ctl 

//输入内容：
#
# sample_csv.ctl -- Control file to load CSV input data
#
#    Copyright (c) 2007-2024, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
#
OUTPUT = zlibrary.isbn                # 导入数据所存放的目的地。
INPUT = /soft/isbn.csv                # 指定导入的数据来源和位置。绝对路径。    
MULTI_PROCESS = YES                   # 开启并行导入。
TRUNCATE = YES                        # 清空数据目标表。
WRITER = DIRECT                       # 导入数据时，绕过共享缓冲区，并跳过WAL日志。 
TYPE = CSV                            # 导入数据的类型
QUOTE = "\""                          # 指定什么字符作为引号。
ESCAPE = \                            # 引号的转义符号。
DELIMITER = ","                       # CSV 文件的分隔符。

对于以上参数详细信息，请参考 pg_bulkload 控制文件参数。

3.3 配置 .pgpass 文件

1. 创建.pgpass 文件。

vi ~/.pgpass

2. 编写免密配置。

//输入内容：
*:*:*:postgres:postgres

3. 更改.pgpass 文件权限。

chmod 600 ~/.pgpass

3.4 导入数据

导入完成之后，共计耗时 1m12.310s 。

time pg_bulkload -U postgres -d testdb /soft/sample_csv.ctl

NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        12180911 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

real    1m12.310s
user    0m0.003s
sys     0m0.006s

4 pg_bulkload 和 copy 命令的性能对比

从下面 copy 实验的结果得出结论：pg_bulkload 要比 copy 命令速度将近快一倍。

4.1 清空数据

清空 zlibrary.isbn 表。

psql -U postgres -d testdb

TRUNCATE TABLE zlibrary.isbn;

4.2 导入数据

 time psql -U postgres -d testdb -c '\copy zlibrary.isbn from /soft/isbn.csv csv'

COPY 12180911

real    2m24.027s
user    0m0.492s
sys     0m0.306s

5 导入数据将会维护索引

1. 使用 pg_bulkload 扩展导入数据时，自动维护相关索引。

2. 请查看索引的详细信息，发现索引的数据量为 471MB。

\x
\di+ zlibrary."isbn_id_2E99E8E3" 

List of relations
-[ RECORD 1 ]-+-----------------
Schema        | zlibrary
Name          | isbn_id_2E99E8E3
Type          | index
Owner         | postgres
Table         | isbn
Persistence   | permanent
Access method | btree
Size          | 471 MB
Description   |